/*
 * AreaOfInterestDAO.java
 *
 * Created on February 12, 2007, 9:10 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package edu.byu.isys413.group1E.data;
import edu.byu.isys413.group1E.*;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author tkulbeth
 */
public class AreaOfInterestDAO {
    
    
    private static AreaOfInterestDAO instance = null;
    
    /** Creates a new instance of AreaOfInterestDAO */
    public AreaOfInterestDAO() {
    }
    
    
    /**Retrieves an instance of the AreaOfInterestDAO object**/
    public static synchronized AreaOfInterestDAO getInstance() {
        if (instance == null) {
            instance = new AreaOfInterestDAO();
        }
        return instance;
    }
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   CREATE
    public AreaOfInterestBO create() throws Exception{
        String id = GUID.generate();
        AreaOfInterestBO bo = new AreaOfInterestBO(id);
        Cache c = Cache.getInstance();
        c.put(bo.getAOI_ID(), bo);
        return bo;
    }
    
    //////////////////////////////////////////////////////////////////////////////
    //////////   READ
    /**
     * This is the public read statement.  It loads an existing record
     * from the database.
     */
    public synchronized AreaOfInterestBO read(String id) throws DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        AreaOfInterestBO aoi = (AreaOfInterestBO)c.get(id);
        if (aoi != null){
            return aoi;                                                        // if so, return it immediately
        }
        
        //otherwise, get it from the database
        try {
            Connection conn = ConnectionPool.getInstance().get();             // retrieve a database connection from the pool
            aoi = this.read(id, conn);                                              // call read with a connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            //rollback
            throw new DataException("Could not retrieve record for id=" + id, e);
        }catch (ConnectionPoolException x) {
            //rollback
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        
        // release the connection back to the pool
        return aoi;
    }
    
    
    synchronized AreaOfInterestBO read(String id, Connection conn) throws SQLException, DataException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        AreaOfInterestBO aoi = (AreaOfInterestBO)c.get(id);
        if (aoi != null){
            return aoi;                                                        // if so, return it immediately
        }
        
        // if not in the cache, get a result set from
        // a SELECT * FROM table WHERE id=guid
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Area_Of_Interest WHERE InterestID = '" + id + "'");
        conn.commit();
        
        //build the object from the result set
        if (rs.next()) {
            aoi = new AreaOfInterestBO(rs.getString("interestid"));
            aoi.setDescription(rs.getString("description"));
            aoi.setAlreadyInDB(true);
            aoi.setIsDirty(false);
            // save to the cache
            c.put(aoi.getAOI_ID(), aoi);
        }else{
            throw new DataException("Object was not found in the database.");
        }
        stmt.close();
        // return the BO
        return aoi;
    }
    
    /*Read entire contents of the AreaOfInterest table*/
    public synchronized ArrayList<AreaOfInterestBO> readList() throws SQLException, DataException, ConnectionPoolException {
        ArrayList<AreaOfInterestBO> aoiList = new ArrayList<AreaOfInterestBO>();
        
        //get connection
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Area_Of_Interest");
        conn.commit();
        
        //build the list with the table data
        while(rs.next()){
            aoiList.add(new AreaOfInterestBO(rs.getString("InterestID"),rs.getString("Description")));
        }
        
        stmt.close();
        //release the connection
        ConnectionPool.getInstance().release(conn);
        //return the list of AOI
        return aoiList;
    }
    
    //////////////////////////////////
    ///   UPDATE
    
    /**
     * This is the public save method.  It is what is called when
     * the user (controller) code wants to save or update an object
     * into the database.
     */
    public synchronized void save(AreaOfInterestBO AOI_In) throws DataException, ConnectionPoolException {
        try {
            Connection conn = ConnectionPool.getInstance().get();
            try {
                this.save(AOI_In, conn);                                                // call save with a connection
                conn.commit();
            } catch (DataException ex) {
                conn.rollback();
                ex.printStackTrace();
            } catch (SQLException ex) {
                conn.rollback();
                ex.printStackTrace();
                throw new SQLException();
            }
            ConnectionPool.getInstance().release(conn);                             // release the connection
        }catch (SQLException e) {
            throw new DataException("Could not retrieve record for id=" + AOI_In.getAOI_ID(), e);
        }catch (ConnectionPoolException x){
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        return;
    }
    
    /**
     *  This is a package method that is called by the public save (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     *
     *  By having other DAOs call this save method (rather than update or
     *  insert below, each DAO in a chained save (like the customerDAO calling
     *  AOI_shipDAO calling InterestDAO save chain) can independently decide
     *  whether to udpate or insert the BO it's saving.  That's why I made
     *  update and insert private rather than package level -- if you call
     *  them directly from another DAO, this DAO can't decide whether it's
     *  object needs to be inserted or updated.
     */
    synchronized void save(AreaOfInterestBO aoi, Connection conn) throws SQLException, DataException {
        // run update or insert
        if (aoi.isIsDirty()) {
            if (aoi.isAlreadyInDB()) {
                update(aoi, conn);
            }else{
                insert(aoi, conn);
            }
            // set the dirty flag to false now that we've saved it
            aoi.setIsDirty(false);
        }
        
        // call save(bo, conn) on any subobjects (like CustomerDAO to AOI_shipDAO)
        // touch the cache for the object
        Cache c = Cache.getInstance();
        c.put(aoi.getAOI_ID(), aoi);
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     */
    private synchronized void update(AreaOfInterestBO aoi, Connection conn) throws SQLException, DataException {
        //update the DB
        PreparedStatement pstmt = conn.prepareStatement("UPDATE Area_Of_Interest SET description = ? WHERE InterestID = ?");
        pstmt.setString(1, aoi.getDescription());
        pstmt.setString(2, aoi.getAOI_ID());
        pstmt.executeUpdate();
        pstmt.close();
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL insert statement and make it more readable.  But
     * logically, it's really just a part of save.
     */
    private synchronized void insert(AreaOfInterestBO aoi, Connection conn) throws SQLException, DataException {
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Area_Of_Interest (InterestID, Description) VALUES (?, ?)");
        pstmt.setString(1, aoi.getAOI_ID());
        pstmt.setString(2, aoi.getDescription());
        pstmt.executeUpdate();
        aoi.setAlreadyInDB(true);
    }
    
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   DELETE
    
    //I did not include the DELETE functionality intentionally due to instructions received, as well as 
    //to protect the integrity of the data.
    
}
